Release 10.1A: OpenEdge Development:
Progress 4GL Reference

DEFINE TEMP-TABLE statement

Defines a temporary table. Progress stores temporary tables on disk in a temporary database. A temporary table can be either global (lasting for the entire OpenEdge session) or local (lasting only as long as the procedure that creates it), and either shared (visible to other procedures that want to access it) or non-shared (visible just to the procedure that created it).

Syntax

DEFINE [ [ NEW [ GLOBAL ] ] SHARED ]   [ PRIVATE | PROTECTED ] TEMP-TABLE temp-table-name [ NO-UNDO ]   [ NAMESPACE-URI namespace ] [ NAMESPACE-PREFIX prefix ]   [ REFERENCE-ONLY ]   [ LIKE table-name [ VALIDATE ] [ USE-INDEX index-name [ AS PRIMARY ] ] ... ]   [ RCODE-INFORMATION ]   [ BEFORE-TABLE before-table-name ]   [ FIELD field-name { AS data-type | LIKE field [ VALIDATE ] }   [ field-options ] ] ... [ INDEX index-name [ IS [ UNIQUE ] [ PRIMARY ] [ WORD-INDEX ] ] { index-field [ ASCENDING | DESCENDING ] } ... ] ...

NEW SHARED TEMP-TABLE temp-table-name

Defines and identifies a temporary table that can be shared by one or more procedures called directly or indirectly by the current procedure. The temporary table remains available to other procedures until the procedure that defined it ends. The called procedures must define the same temporary table name as SHARED.

SHARED TEMP-TABLE temp-table-name

Defines and identifies a temporary table that was initially defined by another procedure as NEW SHARED.

The procedure that establishes the temporary table determines the name. The procedures that share the temporary table use that name to identify it.

NEW GLOBAL SHARED TEMP-TABLE temp-table-name

Defines and identifies a global shared temporary table, and accesses an existing one. The scope of a global shared temporary table is the OpenEdge session. The first procedure to define a temporary table NEW GLOBAL SHARED establishes it. Subsequent procedures access it.

Note: Progress does not establish multiple global shared temporary tables with the same name in the same OpenEdge session.

[ PRIVATE | PROTECTED ] TEMP-TABLE temp-table-name

Defines and identifies a temporary table as a data member for a class, and optionally specifies an access mode for that data member. Do not specify an access mode when defining a temporary table for a method within a class.

PRIVATE data members can be accessed only by the defining class. PROTECTED data members can be accessed by the defining class and any of its inheriting classes. The default access mode is PRIVATE.

Note: These options are applicable only when defining a data member for a class in a class definition (.cls) file.

TEMP-TABLE temp-table-name

Identifies the name of the temporary table. You can define the temporary table in a procedure, a method within a class, or an interface.

NO-UNDO

Specifies that when a transaction is undone, changes to the temporary table records need not be undone. If you do not specify this option, all records in the temporary table are restored to their prior condition when a transaction is undone. The NO-UNDO option can significantly increase the performance for temporary table updates; use it whenever possible.

NAMESPACE-URI namespace

An optional CHARACTER constant that specifies the URI for the namespace of the temp-table.

NAMESPACE-PREFIX prefix

An optional CHARACTER constant that specifies the namespace prefix associated with the NAMESPACE-URI.

REFERENCE-ONLY

Specifies that the procedure defining this temporary table object is using the object definition only as a reference to a temporary table object that is defined and instantiated in another procedure or class, and specified as a parameter in the invocation of a RUN statement, a method in a class, or a user-defined function, using either the BY-REFERENCE or BIND option. Progress does not instantiate the reference-only object.

Passing a reference-only temporary table object parameter to a local routine using either the BY-REFERENCE or BIND option allows the calling routine and the called routine to access the same object instance (instead of deep-copying the parameter).

Note: If you pass the parameter to a remote procedure, Progress deep-copies the parameter on OUTPUT and the reference-only parameter is bound to that copy.

When you pass a temporary table parameter to a local routine using the BY-REFERENCE option, both the calling and called routines access the calling routine’s object instance (and ignore the called routine’s object instance). Since the called routine’s object instance is ignored, you should define the object as a reference-only object. When you define a reference-only temporary table object in the called routine and receive it from the calling routine using the BY-REFERENCE option, Progress binds the definition of the object in the called routine to the object instance in the calling routine for the duration of the called routine. You cannot define a reference-only temporary table object in the calling routine and pass it to the called routine using the BY-REFERENCE option.

When you pass a temporary table parameter to a local routine using the BIND option, you can define a reference-only temporary table object in either the calling routine or the called routine as follows:

When you define a reference-only temporary table object in the calling routine and pass it to the called routine using the BIND option, Progress binds the calling routine to the object instance in the called routine. The reference-only object definition remains bound to the object instance until the routine containing the reference-only object definition is deleted or terminates. The parameter must be an OUTPUT parameter.
Note: If you also define the temporary table object instance in the called routine as a reference-only object, you must bind the object in the called routine before returning to the calling routine.
When you define a reference-only temporary table object in the called routine and receive it from the calling routine using the BIND option, Progress binds the called routine to the object instance in the calling routine. The reference-only object definition remains bound to the object instance until the routine containing the reference-only object definition is deleted or terminates. The parameter must be an INPUT or INPUT-OUTPUT parameter.

In either case, you must specify the BIND option for the parameter in both the invocation of a RUN statement, a method in a class, or a user-defined function, and in the DEFINE PARAMETER statement.

Caution: Do not delete the object or routine to which a reference-only temporary table object is bound, or you might be left with references to an object that no longer exists.

A reference-only temporary table object can be a member of a reference-only ProDataSet object or a standard ProDataSet object. However, if you define a reference-only temporary table in a standard ProDataSet object, you cannot use the ProDataSet object until you bind the reference-only temporary table.

LIKE table-name [ USE-INDEX index-name [ AS PRIMARY ] ] . . .

Specifies the name of a table whose characteristics the temporary table inherits. All field definitions of table-name are added to the temporary table. table-name can represent a database table or another temporary table.

If you reference a database field, the database containing that field must be connected at compile time. If the database field has a validation expression defined in the dictionary that contains a database reference, and the VALIDATE option is specified, the database must also be connected at runtime.

HELP options are inherited from the table-name. Validate options are inherited only if the VALIDATE keyword is used.

Some index definitions from the specified table might also be added to the temporary table:

If you use the USE-INDEX option, only the definitions of indexes you specify with that option are copied to the temporary table. If one of these indexes is the primary index of the LIKE table, it becomes the default primary index of the temporary table. You can, however, use the AS PRIMARY option to override this default primary index.

For example, to make the index country-post the primary index (thereby, overriding the default primary index cust-num in the table customer), you specify it as follows:

DEFINE TEMP-TABLE mycust LIKE customer USE INDEX cust-num USE INDEX country-post AS PRIMARY.

If you do not specify the USE-INDEX option and do not use the INDEX option of the DEFINE TEMP-TABLE statement, then all index definitions are copied from the specified table to the temporary table. In this case, the primary index of the specified table becomes the primary index of the temporary table.
If you do not specify the USE-INDEX option but do use the INDEX option of the DEFINE TEMP-TABLE statement, then no indexes are copied from the specified table.
Progress does not copy inactive indexes to the temporary table.

VALIDATE

The temp-table fields inherit, from the dictionary, validation expressions and validation messages from the database table, table-name.

RCODE-INFORMATION Note: This option is supported only for backward compatibility.

BEFORE-TABLE before-table-name

Specifies the name of the before-image table associated with a static temp-table in a ProDataSet object. You must specify a before-image table name for any static ProDataSet temp-table for which you want to track changes. If you try to modify the records in this before-image table, Progress generates a runtime error.

FIELD field-name

Defines a field in the temporary table. You can use FIELD clauses with the LIKE option to define additional fields for the temporary table, or you can define all your fields with FIELD clauses.

AS data-type

Specifies the data type of the field. The valid data types are BLOB, CHARACTER, CLASS, CLOB, COM-HANDLE, DATE, DATETIME, DATETIME-TZ, DECIMAL, HANDLE, INTEGER, LOGICAL, RAW, RECID, ROWID and WIDGET-HANDLE.

For the CLASS data type, you define a field in a temporary table as a class by specifying the built-in Progress.Lang.Object class name. For example:

DEFINE TEMP-TABLE ttObjHolder FIELD MyObj AS CLASS Progress.Lang.Object.

When you assign a class object instance to a field, Progress implicitly casts the instance to its root super class, which is the Progress.Lang.Object class. After the assignment, the field contains an object reference for a class object instance, not the class itself.

You cannot define a field in a database table as a class.

Note: When a temporary table contains one or more fields defined with the Progress.Lang.Object class, you cannot pass the temporary table to an AppServer.

LIKE field

Specifies a database field or a variable whose characteristics the temporary table field inherits. If you name a variable with this option, that variable must have been defined earlier in the procedure. The temporary table field inherits the data type, extents, format, initial value, label, and column label.

If the database field is a COLUMN-CODEPAGE CLOB, the temp-table field is in the database field’s code page. If the database field is a DBCODEPAGE CLOB, the temp-table field’s code page is -cpinternal.

You can override selected characteristics of the field or variable with the field-options parameter.

If you reference a database field in the LIKE option, the database containing that field must be connected at both compile time and run time. Therefore, use the LIKE option with caution.

field-options

Specifies options for the temporary table field. Any options you specify override any options inherited through the LIKE option. This is the syntax for field-options:

{ [ BGCOLOR expression ] [ COLUMN-LABEL label ] [ DCOLOR expression ] [ DECIMALS n ] [ EXTENT n ] [ FONT expression ] [ FGCOLOR expression ] [ FORMAT string ] [ HELP help-text ] [ INITIAL { constant | { [ constant [ , constant ] ... ] } } ] [ LABEL label [ , label ] ... ] [ MOUSE-POINTER expression ] [ [ NOT ] CASE-SENSITIVE ] [ PFCOLOR expression ]    [ TTCODEPAGE | COLUMN-CODEPAGE codepage ]    [ XML-DATA-TYPE string ] [ XML-NODE-TYPE string ] { [ view-as-phrase ] } }

HELP

A quoted CHARACTER string that represents the help text.

TTCODEPAGE | COLUMN-CODEPAGE codepage

Specifies the code page for a CLOB field in the temporary table. If you specify TTCODEPAGE, the code page is -cpinternal. If you specify COLUMN-CODEPAGE, codepage must be a valid code page name available in the DLC/convmap.cp file. You cannot specify the "undefined" code page for a CLOB. The code page you specify overrides any code page inherited through the LIKE option.

If you do not specify a code page for a CLOB field in the temporary table, the default code page is -cpinternal.

XML-DATA-TYPE string

An optional CHARACTER constant that specifies the XML Schema data type for the field in the temporary table. The XML Schema data type must be compatible with the Progress data type for the field.

For more information about the Progress XML data type mapping rules, see OpenEdge Development: Programming Interfaces .

XML-NODE-TYPE string

An optional CHARACTER constant that specifies the XML node type of the temp-table field, which lets you specify how the field is represented in XML. Valid XML node types are: "ATTRIBUTE", "ELEMENT", "HIDDEN", and "TEXT". The default value is "ELEMENT".

Table 28 lists the valid XML node types.

Table 28: XML node types

When the XML node type is ...	The buffer field is ...
ATTRIBUTE	Represented as an attribute of the temp-table element in both the XML Schema and data.
ELEMENT	Represented as a child element of the temp-table element in both the XML schema and data.
HIDDEN	Omitted from both the XML Schema and data.
TEXT	Represented as a text element in both the XML Schema and data. Note: Each table can contain only one TEXT field. When a table contains a TEXT field, it cannot contain ELEMENT fields; it can contain only ATTRIBUTE fields. A table that contains a TEXT field cannot be part of a nested data-relation.

The XML node type of a temp-table field that represents an array must be either "ELEMENT" or "HIDDEN".

Note: You cannot specify an indeterminate array field in a temp-table using the EXTENT field option.

For more information about the EXTENT field option, and a description of the other field options, see the DEFINE VARIABLE statement.

INDEX index-name [ IS [ UNIQUE ] [ PRIMARY ] [ WORD-INDEX ] ]

Defines an index on the temporary table. To define a unique index, specify the UNIQUE option. To define the primary index, specify the PRIMARY option. To define a word-index, specify the WORD-INDEX option.

If you define more that one index on the temporary table, you can specify PRIMARY for none or one of the indexes. If you specify PRIMARY for none of the indexes, Progress makes the first index you specify the primary index.

If you define no indexes on the temporary table, and the temporary table does not inherit the indexes of another table through the LIKE option of the DEFINE TEMP-TABLE statement, Progress creates a default index, makes it the primary index, and sorts the records in entry order.

index-field [ ASCENDING | DESCENDING ]

Specifies a temporary table field to use as a component of the index. You can use the ASCENDING or DESCENDING option to specify that the component has ascending or descending order.

If you do not specify a sort orientation (ASCENDING or DESCENDING), the index component gets the sort orientation of the previous index component, or, if there is no previous index component, ASCENDING. This rule applies only to index components of temp-tables.

Note: You cannot use a BLOB or CLOB field as a component of an index.

For example, the following two temp-table definitions are equivalent:

DEFINE TEMP-TABLE foo FIELD a AS CHAR FIELD b AS CHAR FIELD c AS CHAR INDEX x a DESC b DESC c DESC.

DEFINE TEMP-TABLE foo FIELD a AS CHAR FIELD b AS CHAR FIELD c AS CHAR INDEX x a DESC b c.

The following two temp-table definitions are also equivalent:

DEFINE TEMP-TABLE foo FIELD a AS CHAR FIELD b AS CHAR FIELD c AS CHAR INDEX x a ASC b DESC c DESC.

DEFINE TEMP-TABLE foo FIELD a AS CHAR FIELD b AS CHAR FIELD c AS CHAR INDEX x a ASC b DESC c.

Examples

The following procedure creates a temporary table (tempitem) that stores the total inventory value (item.price * item.on-hand) for each catalog page (item.cat-page) in the sports database. It builds temp-item with two indexes-one that sorts the table in ascending order by catalog page and a second that sorts the table in descending order by inventory value.

After building temp-item, the procedure displays a dialog box that prompts for report parameters. These parameters include the cutoff value of catalog page inventory to report, and whether to display the report by catalog page (ascending) or inventory value (descending). After displaying the report, the procedure displays another dialog box to repeat the process. The process is repeated until you press the CANCEL button. This procedure shows how you can use a temporary table to store a calculated result from the database, and efficiently report the same result according to different sorting and selection criteria:

r-tmptb1.p

DEFINE TEMP-TABLE temp-item FIELD cat-page LIKE item.cat-page FIELD inventory LIKE item.price LABEL "Inventory Value" INDEX cat-page IS PRIMARY cat-page ASCENDING INDEX inventory-value inventory DESCENDING. DEFINE VARIABLE cutoff LIKE item.price. DEFINE VARIABLE inv-value LIKE item.price. DEFINE VARIABLE report-type AS INTEGER INITIAL 1. DEFINE BUTTON ok-butt LABEL "OK" AUTO-GO. DEFINE BUTTON cancel-butt LABEL "CANCEL" AUTO-ENDKEY. FORM cutoff LABEL "Inventory Lower Cutoff for each Catalog Page" AT ROW 1.25 COLUMN 2 report-type LABEL "Report Sorted ..." AT ROW 2.25 COLUMN 2 VIEW-AS RADIO-SET RADIO-BUTTONS "By Catalog Page", 1, "By Inventory Value", 2 SKIP ok-butt cancel-butt WITH FRAME select-frame SIDE-LABELS WIDTH 70 TITLE "Specify Report ..." VIEW-AS DIALOG-BOX. FOR EACH item BREAK BY item.cat-page: ACCUMULATE price * on-hand (SUB-TOTAL BY item.cat-page). IF LAST-OF(item.cat-page) THEN DO: inv-value = ACCUM SUB-TOTAL BY item.cat-page (price * on-hand). CREATE temp-item. temp-item.cat-page = item.cat-page. inventory = inv-value. END. END. /* FOR EACH item */ ON CHOOSE OF ok-butt DO: HIDE FRAME select-frame. IF report-type = 1 THEN FOR EACH temp-item USE-INDEX cat-page WITH FRAME rpt1-frame: IF inventory >= cutoff THEN DISPLAY temp-item.cat-page inventory. END. ELSE FOR EACH temp-item USE-INDEX inventory-value WITH FRAME rpt2-frame: IF inventory >= cutoff THEN DISPLAY temp-item.cat-page inventory. END. VIEW FRAME select-frame. END. ENABLE ALL WITH FRAME select-frame. WAIT-FOR CHOOSE OF cancel-butt OR WINDOW-CLOSE OF CURRENT-WINDOW.

Notes

If you define a temporary table LIKE a database table, the temporary table does not inherit the database table’s database triggers.
You cannot define a temporary table field of type MEMPTR or LONGCHAR.
You cannot define shared objects, work tables, or temporary tables within an internal procedure, a method in a class, or a user-defined function.
Progress disregards the following when used in conjunction with a temporary table:
The VALIDATE option on a DELETE statement.
The SHARE-LOCK, EXCLUSIVE-LOCK, and NO-LOCK options used with the FIND or FOR statements.
The NO-WAIT option on the FIND statement.
Data handling statements that cause Progress to automatically start a transaction for a regular table will not cause Progress to automatically start a transaction for a temporary table. If you want to start a transaction for operations involving a temporary table, you must explicitly start a transaction by using the TRANSACTION keyword.
Use the CASE-SENSITIVE option only when it is important to distinguish between uppercase and lowercase values entered for a character field. For example, use CASE SENSITIVE to define a field for a part number that contains mixed upper-case and lowercase characters.
You cannot define a SHARED or NEW SHARED temporary table in a class definition (.cls) file. If you do, Progress generates a compilation error.
A SHARED temporary table remains in scope for an instance of a persistent procedure until the instance is deleted. This is true even if the original procedure that defined the temporary table as NEW SHARED goes out of scope while the procedure instance remains persistent.

If a trigger or internal procedure of a persistent procedure executes an external subprocedure that defines a SHARED temporary table, Progress includes the persistent procedure in the resolution of the corresponding NEW SHARED temporary table as though the procedure were on the procedure call stack.

You can specify a join between a temporary table or work table and any appropriate table using the OF keyword. The two tables must contain a commonly named field that participates in a unique index for at least one of the tables. For more information on table joins see the Record phrase reference entry.
If you define a temporary table with the same name as a database table and then you define a buffer for that name, the buffer will be associated with the database table, not with the temporary table.
See OpenEdge Development: Progress 4GL Handbook for information on temporary tables and work tables.

See also

CREATE TEMP-TABLE statement, DEFINE DATASET statement, DEFINE WORK-TABLE statement, NUM-REFERENCES attribute, RUN statement